﻿/*******************************************************************************
 * Copyright © 2017 FriendStar.Framework 版权所有
 * Author: FZH
 * Description: FriendStar快速开发平台
 * Website：http://www.FriendStar.cn
*********************************************************************************/


using FriendStar.Application.VBaseData;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using FriendStar.Code;
using System.Text;
using System.Drawing;
using System.IO;

namespace FriendStar.Application.ProductionPlan
{
    /// <summary>
	/// 材料采购对应的数据应用功能类。
	/// </summary>
	public class PreparationMaterialsApp
    {
        public object GetFProductionTypies(string FClassNO, string UseUnit)
        {
            List<Dictionary<string, object>> result = null;

            string sql = @"SELECT
	                        FTypeNO + ' ' + FTypeName FTypeNOName,
                        FTypeNO,
                        FTypeName
                        FROM
	                        V_BUS_SJ_Dictionary
                        WHERE
                            UseUnit = @UseUnit and
	                        FClassNO = @FClassNO
                        ORDER BY
	                        FClassNO";

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@FClassNO", FClassNO) ,
                new SqlParameter("@UseUnit", UseUnit)
            };

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetMaterialOperationDetails(string matNO, string useUnit,bool isArchive)
        {
            Dictionary<string, object> result = null;

            string sql = @"select 
                                    MatNo,
                                    InputDate,
	                                ProductDate,
	                                FAuditingDate,
	                                FCXBH,
	                                ProductionNO,
	                                FProductionName,
	                                DecomposeState,
	                                MaterialSizes_J,
	                                FiberDirection,
	                                KFGC,
	                                KZGC,
	                                CFGC,
	                                CZGC,
	                                RoughsType,
	                                FJHCL,
	                                TLCL,
	                                MaintainStatus,
	                                Fcontent,
	                                FJHZL,
	                                TechWear,
	                                isJQ,
	                                Features,
	                                MaterialCode_YCL,
	                                MaterialSizes_YCL,
	                                WorkShopName,
	                                MaterialCode_BL,
	                                MaterialSizes_BL,
	                                MaterialCode_SJJL,
	                                MaterialSizes_SJJL,
	                                MaterialSizes_K,
	                                Plane_KJZS,
	                                Plane_KJZL,
	                                Plane_KJZS + Plane_SJZS Plane_KJZS_Plane_SJZS,
	                                MaterialCode_SJBL,
	                                MaterialSizes_SJBL,
	                                Plane_SJZS,
	                                Plane_SJZL,
	                                Plane_KJZL +  Plane_SJZL Plane_KJZL_Plane_SJZL,
	                                FYZJK_GY,
	                                FYZJK,
	                                FErrorK1,
	                                FYKJJ_GY,
	                                FYKJJ,
	                                FErrorK2,
	                                DJJHDE,
	                                DJSJDE,
	                                FYZJK_GY * FYKJJ_GY FYZJK_GY_FYKJJ_GY,
	                                FYZJK * FYKJJ FYZJK_FYKJJ,
	                                FErrorK3,
	                                YPJHDE,
	                                YPTLDE,
	                                FAuditingBy,
	                                FIsReturn,
	                                LPH,
	                                FCGRY,
	                                FCGRQ,
	                                FSHZS,
	                                FSHZL,
	                                Fbatch,
	                                FCKRY,
	                                FCKRQ,
	                                FCKJG,
	                                FLGG1,
	                                FLZL1,
	                                FSLRY,
	                                FSLRQ,
	                                FSLSL,
	                                FLGG2,
	                                FLZL2,
	                                FJQRY,
	                                FJQRQ,
	                                FJQJG,
	                                FCJRY,
	                                FCJRQ,
	                                FCJSL,
	                                YLGG1,
	                                YLSL1,
	                                YLGG2,
	                                YLSL2,
	                                FBZSM
                                from {0}
                where  matNO=@matNO   and useUnit = @useUnit", tableName = "V_LJPC_TL_RunDouListAcco";

            result = new Dictionary<string, object>();

            if (isArchive)
            {
                tableName = "V_LJPC_TL_RunDouListAcco_da";
            }

            sql = string.Format(sql,  tableName);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit),
                new SqlParameter("@matNO", matNO)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                if (reader.Read())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        result.Add(reader.GetName(i), reader[i]);
                    }
                }
            }

            return result;
        }

        public object GetRequireCompanies(string UseUnit)
        {
            List<Dictionary<string, object>> result = null;

            Dictionary<string, object> item = null;

            string sql = @"SELECT
	                        MakeLineNO,
	                        MakeLineName,
                        MakeLineNO + ' ' + MakeLineName MakeLineNOName
                        FROM
	                        LXK_16_LX_MakeLine
                        where useUnit = @useUnit
                        ORDER BY
	                        MakeLineNO
                        ";

            result = new List<Dictionary<string, object>>();

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@UseUnit", UseUnit)
            };

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object DeleteMaterialOperation(string id, string useUnit)
        {
            int result = 0;

            string sql = @"DELETE FROM LJPC_TL_RunDouListAcco  where UseUnit = @UseUnit AND MatNO ='{0}'";

            sql = string.Format(sql, id);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@UseUnit", useUnit)
            };

            result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, pars);

            return result;
        }

        public object GetMaterialOperationPrintData(string MatNO, bool IsArchive, string UseUnit)
        {
            Dictionary<string, object> result = null;

            string sql = @"SELECT
	                        *,Plane_KJZS+Plane_SJZS Plane_KJZS_Plane_SJZS,
Plane_KJZL+Plane_SJZL Plane_KJZL_Plane_SJZL,
FYZJK_GY*FYKJJ_GY FYZJK_GY_FYKJJ_GY,
FYZJK*FYKJJ FYZJK_FYKJJ,Fbatch,IsImg

                        FROM
	                        {0}
                        where useUnit = @useUnit and MatNO= '{1}'
                        ORDER BY
	                        MatNO
                        ", tableName = "V_LJPC_TL_RunDouListAcco";

            if (IsArchive)
            {
                tableName = "V_LJPC_TL_RunDouListAcco_DA";
            }

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@UseUnit", UseUnit)
            };
            result = new Dictionary<string, object>();

            sql = string.Format(sql, tableName, MatNO);

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                if (reader.Read())
                {


                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        result.Add(reader.GetName(i), reader[i]);
                    }
                }
            }

            return result;
        }

        public Bitmap GetMaterialOperationImage(string Pic_ID)
        {
            Bitmap result = null;

            string sql = @"SELECT
	                        FIMG
                        FROM
	                        LJPC_TL_RunDouListAcco_Img
                        where Pic_ID = @Pic_ID";

            SqlParameter par = new SqlParameter("@Pic_ID", Pic_ID);

            string base64Str = SqlHelper.ExecuteScalar(SqlHelper.GetConnection(), CommandType.Text, sql, par).ToString();

           byte[] bt = Convert.FromBase64String(base64Str);

            MemoryStream ms = new MemoryStream(bt);

            result = (Bitmap)Image.FromStream(ms);

            ms.Close();
            
            return result;
        }

        public object PublishMaterialOperations(string ids, string useUnit, string curUserName, string curDate, string curTime)
        {
            int result = 0;

            string sql = @"UPdate LJPC_TL_RunDouListAcco  set MaintainStatus ='1',FAuditingBy=@FAuditingBy,FAuditingDate=@FAuditingDate,FAuditingTime=@FAuditingTime  where UseUnit = @UseUnit AND MatNO in('{0}');
                            DELETE FROM LJPC_TL_RunDouListAcco_DA  where UseUnit =@UseUnit AND MatNO in('{0}');						
                            INSERT INTO LJPC_TL_RunDouListAcco_DA  SELECT *  FROM LJPC_TL_RunDouListAcco where UseUnit = @UseUnit AND  MatNO in('{0}')	;						
                            DELETE FROM LJPC_TL_RunDouListAcco  where UseUnit = @UseUnit AND  MatNO in('{0}'); ";

            sql = string.Format(sql, string.Join("','", ids.Split(',')));

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@UseUnit", useUnit),
                new SqlParameter("@FAuditingBy", curUserName),
                new SqlParameter("@FAuditingDate", curDate),
                new SqlParameter("@FAuditingTime", curTime)
            };

            result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, pars);

            return result;
        }

        public object InvalidMaterialOperation(string id, string useUnit)
        {
            int result = 0;

            string sql = @"update LJPC_TL_RunDouListAcco_da  set MaintainStatus ='2' where UseUnit = @UseUnit AND MatNO ='{0}'";

            sql = string.Format(sql, id);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@UseUnit", useUnit)
            };

            result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, pars);

            return result;
        }

        public object GetMaterialOperationsArchive(Pagination pagination, string ProductMonth, string MaintainStatus, string RequireCompanyNO, string MatNO, string ProductionNO, string FProductionName, string MaterialCode_SJJL, string MaterialSizes_SJJL, string UseUnit)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" select InputDate + ' ' + InputTime FCreateDate_FCreateTime,
	                                    ProductDate,
	                                    MatNO,
	                                    MaintainStatus,
	                                    isJQ,
	                                    FIsWW,
	                                    IsBZGY,
	                                    IsImg,
	                                    ProductCompany,
	                                    ProductCompanyName,
	                                    WorkShop,
	                                    WorkShopName,
	                                    RequireCompanyNO,
	                                    RequireCompanyName,
	                                    ProCompanyNO,
	                                    ProCompanyName,
	                                    ProductionNO,
	                                    FProductionName,
	                                    Fcontent,
	                                    TLCL,
                                        TLZL,
	                                    Actual_ZTLZS,
	                                    LYL_TL,
	                                    Plane_KJZS,
	                                    Plane_KJZL,
	                                    Plane_SJZS,
	                                    Plane_SJZL,
	                                    MaterialNO,
	                                    MaterialName,
	                                    isnull(MaterialCode_YCL, '') + '|' + isnull(MaterialCode_SJJL, '') MaterialCode_YCL_MaterialCode_SJJL,
	                                    isnull(MaterialSizes_YCL, '') + '|' + isnull(MaterialSizes_BL, '') MaterialSizes_YCL_MaterialSizes_BL,
	                                    isnull(MaterialCode_BL, '') + '|' + isnull(MaterialCode_SJBL, '') MaterialCode_BL_MaterialCode_SJBL,
	                                    isnull(MaterialSizes_BL, '') + '|' + isnull(MaterialSizes_SJBL, '') MaterialSizes_BL_MaterialSizes_SJBL,
	                                    CASE FiberDirection
                                    WHEN 'A' THEN
	                                    '是'
                                    ELSE
	                                    ''
                                    END FiberDirection,
                                     Sign_MP,
                                     CASE RoughsType
                                    WHEN 'K' THEN
	                                    '块料'
                                    WHEN 'T' THEN
	                                    '条料'
                                    WHEN 'L' THEN
	                                    '落料'
                                    WHEN 'Y' THEN
	                                    '异形'
                                    ELSE
	                                    ''
                                    END RoughsType,
                                     RoughsSize,
                                     isnull(KFGC,'') +'|'+isnull(KZGC,'') KFGC_KZGC,
                                     KZGC,
                                     KFGC,
                                     isnull(CFGC,'') +'|'+isnull(CZGC,'') CFGC_CZGC,
                                     CFGC,
                                     CZGC,
                                     FYZJK,
                                     FYKJJ,
                                     FYZJK*FYKJJ FYZJK_FYKJJ,
                                     KZLJ,
                                     DJJHDE,
                                     DJSJDE,
                                     YPJHDE,
                                     YPTLDE,
                                     PartsNetWeight,
                                     MaterialQuota,
                                     LYL_GY,
                                     YLGG1,
                                     YLSL1,
                                     YLGG2,
                                     YLSL2,
                                     YLGG3,
                                     YLSL3,
                                     ACTUAL_CLBH,
                                     Actual_CLBK,
                                     Actual_LLJC,
                                     Actual_BLZS,
                                     Actual_TLTLSL,
                                     Actual_TLLJSL,
                                     BTGG,
                                     BTZL,
                                     BWGG,
                                     BWZL,
                                     FLGG1,
                                     FLZL1,
                                     FLGG2,
                                     FLZL2,
                                     FLGG3,
                                     FLZL3,
                                     Actual_CLLLZL,
                                     Actual_CKCLZL,
                                     InputUser,
                                     InputDate,
                                     InputTime,
                                     FAuditingBy,
                                     FAuditingDate,
                                     FAuditingTime,
                                     FCGRY,
                                     FCGRQ,
                                     FCGRSJ,
                                     FCKRY,
                                     FCKRQ,
                                     FCKSJ,
                                     FSHZS,
                                     FSHZL,
                                     FCKJG,
                                     FSLRY,
                                     FSLRQ,
                                     FSLRSJ,
                                     FSLSL,
                                     FJQRY,
                                     FJQRQ,
                                     FJQSJ,
                                     FJQJG,
                                     FCJRY,
                                     FCJRQ,
                                     FCJRSJ,
                                     FCJSL,
                                     FBZSM,
                                    ProductMonth
                                from(
                                select  row_number () over (order by {4}) num, *
                                from {3}
                                )a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_LJPC_TL_RunDouListAcco_da";



            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!string.IsNullOrEmpty(ProductMonth))
            {
                conn.Append(string.Format(" and  ProductMonth = '{0}'", ProductMonth));
            }

            if (!string.IsNullOrEmpty(MaintainStatus))
            {
                conn.Append(string.Format(" and MaintainStatus = '{0}'", MaintainStatus));
            }

            if (!string.IsNullOrEmpty(RequireCompanyNO))
            {
                conn.Append(string.Format(" and RequireCompanyNO = '{0}'", RequireCompanyNO));
            }

            if (!string.IsNullOrEmpty(MatNO))
            {
                conn.Append(string.Format(" and MaterialCode_SJBL like '%{0}%'", MatNO));
            }

            if (!string.IsNullOrEmpty(ProductionNO))
            {
                conn.Append(string.Format(" and ProductionNO like '%{0}%'", ProductionNO));
            }
            if (!string.IsNullOrEmpty(FProductionName))
            {
                conn.Append(string.Format(" and  FProductionName like '%{0}%'", FProductionName));
            }
            if (!string.IsNullOrEmpty(MaterialCode_SJJL))
            {
                conn.Append(string.Format(" and MaterialCode_SJJL like '%{0}%'", MaterialCode_SJJL));
            }
            if (!string.IsNullOrEmpty(MaterialSizes_SJJL))
            {
                conn.Append(string.Format(" and RTRIM(LTRIM(MaterialSizes_SJJL)) like '%{0}%'", MaterialSizes_SJJL));
            }
            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", UseUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetMaterialOperationsEdit(Pagination pagination, string ProductMonth, string MaintainStatus, string RequireCompanyNO, string MatNO, string ProductionNO, string FProductionName, string MaterialCode_SJJL, string MaterialSizes_SJJL, string useUnit)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @" select InputDate + ' ' + InputTime FCreateDate_FCreateTime,
	                                    ProductDate,
	                                    MatNO,
	                                    MaintainStatus,
	                                    isJQ,
	                                    FIsWW,
	                                    IsBZGY,
	                                    IsImg,
	                                    ProductCompany,
	                                    ProductCompanyName,
	                                    WorkShop,
	                                    WorkShopName,
	                                    RequireCompanyNO,
	                                    RequireCompanyName,
	                                    ProCompanyNO,
	                                    ProCompanyName,
	                                    ProductionNO,
	                                    FProductionName,
	                                    Fcontent,
	                                    TLCL,
                                        TLZL,
	                                    Actual_ZTLZS,
	                                    LYL_TL,
	                                    Plane_KJZS,
	                                    Plane_KJZL,
	                                    Plane_SJZS,
	                                    Plane_SJZL,
	                                    MaterialNO,
	                                    MaterialName,
	                                    isnull(MaterialCode_YCL, '') + '|' + isnull(MaterialCode_SJJL, '') MaterialCode_YCL_MaterialCode_SJJL,
	                                    isnull(MaterialSizes_YCL, '') + '|' + isnull(MaterialSizes_BL, '') MaterialSizes_YCL_MaterialSizes_BL,
	                                    isnull(MaterialCode_BL, '') + '|' + isnull(MaterialCode_SJBL, '') MaterialCode_BL_MaterialCode_SJBL,
	                                    isnull(MaterialSizes_BL, '') + '|' + isnull(MaterialSizes_SJBL, '') MaterialSizes_BL_MaterialSizes_SJBL,
	                                    CASE FiberDirection
                                    WHEN 'A' THEN
	                                    '是'
                                    ELSE
	                                    ''
                                    END FiberDirection,
                                     Sign_MP,
                                     CASE RoughsType
                                    WHEN 'K' THEN
	                                    '块料'
                                    WHEN 'T' THEN
	                                    '条料'
                                    WHEN 'L' THEN
	                                    '落料'
                                    WHEN 'Y' THEN
	                                    '异形'
                                    ELSE
	                                    ''
                                    END RoughsType,
                                     RoughsSize,
                                     isnull(KFGC,'') +'|'+isnull(KZGC,'') KFGC_KZGC,
                                     KZGC,
                                     KFGC,
                                     isnull(CFGC,'') +'|'+isnull(CZGC,'') CFGC_CZGC,
                                     CFGC,
                                     CZGC,
                                     FYZJK,
                                     FYKJJ,
                                     FYZJK*FYKJJ FYZJK_FYKJJ,
                                     KZLJ,
                                     DJJHDE,
                                     DJSJDE,
                                     YPJHDE,
                                     YPTLDE,
                                     PartsNetWeight,
                                     MaterialQuota,
                                     LYL_GY,
                                     YLGG1,
                                     YLSL1,
                                     YLGG2,
                                     YLSL2,
                                     YLGG3,
                                     YLSL3,
                                     ACTUAL_CLBH,
                                     Actual_CLBK,
                                     Actual_LLJC,
                                     Actual_BLZS,
                                     Actual_TLTLSL,
                                     Actual_TLLJSL,
                                     BTGG,
                                     BTZL,
                                     BWGG,
                                     BWZL,
                                     FLGG1,
                                     FLZL1,
                                     FLGG2,
                                     FLZL2,
                                     FLGG3,
                                     FLZL3,
                                     Actual_CLLLZL,
                                     Actual_CKCLZL,
                                     InputUser,
                                     InputDate,
                                     InputTime,
                                     FAuditingBy,
                                     FAuditingDate,
                                     FAuditingTime,
                                     FCGRY,
                                     FCGRQ,
                                     FCGRSJ,
                                     FCKRY,
                                     FCKRQ,
                                     FCKSJ,
                                     FSHZS,
                                     FSHZL,
                                     FCKJG,
                                     FSLRY,
                                     FSLRQ,
                                     FSLRSJ,
                                     FSLSL,
                                     FJQRY,
                                     FJQRQ,
                                     FJQSJ,
                                     FJQJG,
                                     FCJRY,
                                     FCJRQ,
                                     FCJRSJ,
                                     FCJSL,
                                     FBZSM,
                                    ProductMonth
                                from(
                                select  row_number () over (order by {4}) num, *
                                from {3}
                                )a
                where  num > {0} and num <= {1}  and useUnit = @useUnit {2}", tableName = "V_LJPC_TL_RunDouListAcco";



            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!string.IsNullOrEmpty(ProductMonth))
            {
                conn.Append(string.Format(" and ProductMonth = '{0}'", ProductMonth));
            }

            if (!string.IsNullOrEmpty(MaintainStatus))
            {
                conn.Append(string.Format(" and MaintainStatus = '{0}'", MaintainStatus));
            }

            if (!string.IsNullOrEmpty(RequireCompanyNO))
            {
                conn.Append(string.Format(" and RequireCompanyNO = '{0}'", RequireCompanyNO));
            }

            if (!string.IsNullOrEmpty(MatNO))
            {
                conn.Append(string.Format(" and MaterialCode_SJBL like '%{0}%'", MatNO));
            }

            if (!string.IsNullOrEmpty(ProductionNO))
            {
                conn.Append(string.Format(" and ProductionNO like '%{0}%'", ProductionNO));
            }
            if (!string.IsNullOrEmpty(FProductionName))
            {
                conn.Append(string.Format(" and  FProductionName like '%{0}%'", FProductionName));
            }
            if (!string.IsNullOrEmpty(MaterialCode_SJJL))
            {
                conn.Append(string.Format(" and MaterialCode_SJJL like '%{0}%'", MaterialCode_SJJL));
            }
            if (!string.IsNullOrEmpty(MaterialSizes_SJJL))
            {
                conn.Append(string.Format(" and RTRIM(LTRIM(MaterialSizes_SJJL)) like '%{0}%'", MaterialSizes_SJJL));
            }
            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@useUnit", useUnit)
            };
            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public Dictionary<string, string> GetFProductionTypeDics(string FClassNO, string UseUnit)
        {
            Dictionary<string, string> result = null;

            string sql = @"SELECT
                        FTypeNO,
                        FTypeName
                        FROM
	                        V_BUS_SJ_Dictionary
                        WHERE
                            UseUnit = @UseUnit and
	                        FClassNO = @FClassNO
                        ORDER BY
	                        FClassNO";

            result = new Dictionary<string, string>();

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@FClassNO", FClassNO) ,
                new SqlParameter("@UseUnit", UseUnit)
            };

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    result.Add(reader["FTypeName"].ToString(), reader["FTypeNO"].ToString());
                }
            }

            return result;
        }

        public object AddContractRegistration(Dictionary<string, object> item)
        {
            int result = 0;

            string sql = @"insert into  MCG_11_HT_MaterialPurchaseDJ ({0}) values({1})";

            List<string> conn = null, conn2 = null, columns = null;

            SqlParameter[] pars = null;

            pars = new SqlParameter[item.Count];

            conn = new List<string>();

            conn2 = new List<string>();

            columns = new List<string>();

            foreach (string key in item.Keys)
            {
                conn.Add(string.Format("{0}", key));

                conn2.Add(string.Format("@{0}", key));

                columns.Add(key);
            }

            sql = string.Format(sql, string.Join(",", conn.ToArray()), string.Join(",", conn2.ToArray()));

            for (int i = 0; i < item.Keys.Count; i++)
            {
                pars[i] = new SqlParameter(string.Format("@{0}", columns[i]), item[columns[i]]);
            }

            result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, pars);

            return result;
        }

        public object PublishContractRegistration(string ids, string curUser, string curDate, string curTime)
        {
            int result = 0;

            string sql = @"update MCG_11_HT_MaterialPurchaseDJ 
                                set CGFBRY = '{1}' ,
                                     CGFBRQ  = '{2}',
                                     CGFBSJ = '{3}',
                                     CGWHZT = '1'
                                 where ID in ('{0}');
                                delete from MCG_11_HT_MaterialPurchaseDJ_da where ID in ('{0}');
                                insert into MCG_11_HT_MaterialPurchaseDJ_da
                                select * from
                                MCG_11_HT_MaterialPurchaseDJ
                                where ID in ('{0}');";


            sql = string.Format(sql, string.Join("','", ids.Split(',')), curUser, curDate, curTime);

            result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql);

            return result;
        }

        public object ImportContractRegistration(string date, Dictionary<string, object> dt, string useUnit, string curUser, string curDate, string curTime)
        {
            int result = 0;
            //材料牌号 材料规格	产地	供应商	到站	采购方式	采购分类	分流	用途	合同编号	订货数量	采购单价	到站单价
            string res = "CGCLPH,CGCLGG,CGCD,CGGHDW,CGDZ,CGCGFS,CGCGFL,CGLX,CGYT,CGHTBH,CGDHSL,CGCGDJ,CGDZDJ,CLFL,UseUnit,CGDJRY,CGDJRQ,CGDJSJ";

            string[] cs = "材料牌号,材料规格,产地,供应商,到站,采购方式,采购分类,分流,用途,合同编号,订货数量,采购单价,到站单价,材料分类,用户单位,登记用户,登记日期,登记时间".Split(',');

            string sqlTemplate = @"insert into MCG_11_HT_MaterialPurchaseDJ(CGHTRQ,{0}) values('{1}');";

            StringBuilder sql = new StringBuilder();

            string deleteSQL = "delete from MCG_11_HT_MaterialPurchaseDJ where CGHTBH in ('{0}');";

            List<string> CGHTBHS = new List<string>();

            List<Dictionary<string, object>> map = dt["Sheet1"] as List<Dictionary<string, object>>;
            //产地
            Dictionary<string, string> CGCDS = GetFProductionTypeDics("40", useUnit);
            //	供应商
            Dictionary<string, string> CGGHDWS = GetSupplierInformationDics(useUnit, 0);
            //	到站
            Dictionary<string, string> CGDZS = GetFProductionTypeDics("39", useUnit);
            //	采购方式
            Dictionary<string, string> CGCGFSS = GetFProductionTypeDics("37", useUnit);
            //	采购分类	
            Dictionary<string, string> CGCGFLS = GetFProductionTypeDics("38", useUnit);
            //分流
            Dictionary<string, string> CGLXS = GetSupplierInformationDics(useUnit, 1);
            //用途
            Dictionary<string, string> CGYTS = GetFProductionTypeDics("41", useUnit);
            //材料分类
            List<Dictionary<string, object>> CLFLS = GetMetrialCategories();

            foreach (Dictionary<string, object> item in map)
            {
                CGHTBHS.Add(string.Format("{0}", item["合同编号"]));
            }

            sql.AppendLine(string.Format(deleteSQL, string.Join("','", CGHTBHS.ToArray())));

            foreach (Dictionary<string, object> item in map)
            {
                List<string> cols = new List<string>();

                try
                {
                    item["产地"] = CGCDS[item["产地"].ToString()];
                }
                catch
                {
                    item["产地"] = string.Empty;
                }

                try
                {
                    item["供应商"] = CGGHDWS[item["供应商"].ToString()];
                }
                catch
                {
                    item["供应商"] = string.Empty;
                }

                try
                {
                    item["到站"] = CGDZS[item["到站"].ToString()];
                }
                catch
                {
                    item["到站"] = string.Empty;
                }

                try
                {
                    item["采购方式"] = CGCGFSS[item["采购方式"].ToString()];
                }
                catch
                {
                    item["采购方式"] = string.Empty;
                }

                try
                {
                    item["采购分类"] = CGCGFLS[item["采购分类"].ToString()];
                }
                catch
                {
                    item["采购分类"] = string.Empty;
                }

                try
                {
                    item["分流"] = CGLXS[item["分流"].ToString()];
                }
                catch
                {
                    item["分流"] = string.Empty;
                }
                try
                {
                    item["用途"] = CGYTS[item["用途"].ToString()];
                }
                catch
                {
                    item["用途"] = string.Empty;
                }

                item["材料规格"] = item["材料规格"].ToString().Replace("*", "x");

                Dictionary<string, object> finder = CLFLS.Find(v => v["MaterialCode"].ToString().Equals(item["材料牌号"]) && v["MaterialSizes"].ToString().Equals(item["材料规格"]));
                //,CLFL,UseUnit,CGDJRY,CGDJRQ,CGDJSJ
                try
                {
                    item["材料分类"] = finder["MaterialNO"].ToString();
                }
                catch
                {
                    item["材料分类"] = string.Empty;
                }
                item["用户单位"] = useUnit;

                item["登记用户"] = curUser;

                item["登记日期"] = curDate;

                item["登记时间"] = curTime;

                item["采购单价"] = string.Format("{0}", item["采购单价"]).Trim().Equals(string.Empty) ? "0" : string.Format("{0}", item["采购单价"]).Trim();

                item["到站单价"] = string.Format("{0}", item["到站单价"]).Trim().Equals(string.Empty) ? "0" : string.Format("{0}", item["到站单价"]).Trim();

                cols.Add(date);

                foreach (string c in cs)
                {
                    cols.Add(string.Format("{0}", item[c]));
                }

                sql.AppendLine(string.Format(sqlTemplate, res, string.Join("','", cols.ToArray())));
            }

            result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql.ToString());

            return result;
        }

        public object GetMaterialCategoriesByPage(Pagination pagination, string MaterialNO, string MaterialCode, string MaterialSizes, string UseUnit)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"SELECT
                            MaterialNO,
	                    MaterialName,
	                    MaterialCode,
	                    MaterialSizes,
	                    StandardNumber,
	                    MaterialDemand
                        FROM
	                        (
		                        SELECT
			                        row_number () OVER (ORDER BY {4}) num,
			                        *
		                        FROM
			                        {3}
	                        ) aa
                where  num > {0} and num <= {1}  {2}", tableName = "V_CLDE_51_GG_CLDEerialVarieties";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!string.IsNullOrEmpty(MaterialNO))
            {
                conn.Append(string.Format(" and MaterialNO = '{0}'", MaterialNO));
            }

            if (!string.IsNullOrEmpty(MaterialCode))
            {
                conn.Append(string.Format(" and MaterialCode like '%{0}%'", MaterialCode));
            }

            if (!string.IsNullOrEmpty(MaterialSizes))
            {
                conn.Append(string.Format(" and MaterialSizes like '%{0}%'", MaterialSizes));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@UseUnit",UseUnit)
            };

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object DeleteContractRegistration(string ids)
        {
            int result = 0;

            string sql = @"delete from MCG_11_HT_MaterialPurchaseDJ 
            where ID in({0})";

            List<string> conn = null;

            List<string> cols = null;

            conn = new List<string>(ids.Split(','));

            cols = new List<string>();

            foreach (string con in conn)
            {
                cols.Add(string.Format("'{0}'", con));
            }

            sql = string.Format(sql, string.Join(",", cols.ToArray()));

            result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql);

            return result;
        }

        public object UpdateContractRegistration(Dictionary<string, object> item)
        {
            int result = 0, count = item.Count;

            string sql = @"update MCG_11_HT_MaterialPurchaseDJ set 
            {0}
            where ID =@ID";

            List<string> conn = null, columns = null;

            SqlParameter[] pars = null;

            string moldid = item["ID"].ToString();

            DateTime now = DateTime.Now;

            item.Remove("ID");

            pars = new SqlParameter[count];

            conn = new List<string>();

            columns = new List<string>();

            foreach (string key in item.Keys)
            {
                conn.Add(string.Format("{0} = @{0}\r\n", key));

                columns.Add(key);
            }

            sql = string.Format(sql, string.Join(",", conn.ToArray()));

            for (int i = 0; i < item.Keys.Count; i++)
            {
                pars[i] = new SqlParameter(string.Format("@{0}", columns[i]), item[columns[i]]);
            }

            pars[item.Count] = new SqlParameter("@ID", moldid);

            result = SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, pars);

            return result;
        }

        public object GetMaterialCategories(string UseUnit)
        {
            //select MaterialNO,MaterialName  from dbo.CLDE_ZD_FL_CLDEerialClassNO  order by MaterialNO
            List<Dictionary<string, object>> result = null;

            string sql = @"SELECT
	                            MaterialNO,
	                            MaterialName,
                            MaterialNO + ' ' + MaterialName MaterialNOName
                            FROM
	                            dbo.CLDE_ZD_FL_CLDEerialClassNO
                            WHERE
	                            UseUnit = @UseUnit
                            ORDER BY
	                            MaterialNO";

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@UseUnit", UseUnit)
            };

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetContractRegistrationsAdd(Pagination pagination, string CGCD, string CGDZ, string CGCGFL, string CGCGFS, string CGYT, string CGGHDW, string CGCGFLiu, string UseUnit)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"SELECT
                            ID + '|' + isnull(CGWHZT,'0') IDCGWHZT,
                            ID,
                            CGHTRQ,
	                        CLFL,
	                        CLFLMC,
	                        CGCLPH,
	                        CGCLGG,
	                        CGCD,
	                        CGCDMC,
	                        CGGHDW,
	                        CGGHDWMC,
	                        CGDZ,
	                        CGDZMC,
	                        CGCGFS,
	                        CGCGFSMC,
	                        CGCGFL,
	                        CGCGFLMC,
	                        CGYT,
	                        CGYTMC,
	                        CGHTBH,
	                        CGDHSL,
	                        CGCGDJ,
	                        CGDZDJ,
	                        CGCLBZBH,
	                        CGCLTSYQ,
	                        CGBHSM,
	                        CGDJRY,
	                        CGDJRQ,
	                        CGDJSJ,
	                        CGFBRY,
	                        CGFBSJ,
	                        case CGWHZT when 1 then '发布' else  '未发布' end CGWHZT,
                            CGLX
                        FROM
	                        (
		                        SELECT
			                        row_number () OVER (ORDER BY {4}) num,
			                        *
		                        FROM
			                        {3}
	                        ) aa
                where  num > {0} and num <= {1}  {2}", tableName = "V_MCG_11_HT_MaterialPurchaseDJ";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();


            if (!string.IsNullOrEmpty(CGCD))
            {
                conn.Append(string.Format(" and CGCD = '{0}'", CGCD));
            }

            if (!string.IsNullOrEmpty(CGDZ))
            {
                conn.Append(string.Format(" and CGDZ = '{0}'", CGDZ));
            }

            if (!string.IsNullOrEmpty(CGCGFL))
            {
                conn.Append(string.Format(" and CGCGFL = '{0}'", CGCGFL));
            }

            if (!string.IsNullOrEmpty(CGCGFS))
            {
                conn.Append(string.Format(" and CGCGFS = '{0}'", CGCGFS));
            }

            if (!string.IsNullOrEmpty(CGYT))
            {
                conn.Append(string.Format(" and CGYT = '{0}'", CGYT));
            }

            if (!string.IsNullOrEmpty(CGGHDW))
            {
                conn.Append(string.Format(" and CGGHDW = '{0}'", CGGHDW));
            }

            if (!string.IsNullOrEmpty(CGCGFLiu))
            {
                conn.Append(string.Format(" and CGLX = '{0}'", CGCGFLiu));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetContractRegistrationsArchive(Pagination pagination, string CGCD, string CGDZ, string CGCGFL, string CGCGFS, string CGYT, string CGGHDW, string CGCGFLiu, string UseUnit)
        {
            List<Dictionary<string, object>> result = null;

            int start = 0, end = 0;

            string sql = @"SELECT
                            ID,
                            CGHTRQ,
	                        CLFL,
	                        CLFLMC,
	                        CGCLPH,
	                        CGCLGG,
	                        CGCD,
	                        CGCDMC,
	                        CGGHDW,
	                        CGGHDWMC,
	                        CGDZ,
	                        CGDZMC,
	                        CGCGFS,
	                        CGCGFSMC,
	                        CGCGFL,
	                        CGCGFLMC,
	                        CGYT,
	                        CGYTMC,
	                        CGHTBH,
	                        CGDHSL,
	                        CGCGDJ,
	                        CGDZDJ,
	                        CGCLBZBH,
	                        CGCLTSYQ,
	                        CGBHSM,
	                        CGDJRY,
	                        CGDJRQ,
	                        CGDJSJ,
	                        CGFBRY,
	                        CGFBSJ,
	                        case CGWHZT when 1 then '发布' else  '未发布' end CGWHZT,
                            CGLX
                        FROM
	                        (
		                        SELECT
			                        row_number () OVER (ORDER BY {4}) num,
			                        *
		                        FROM
			                        {3}
	                        ) aa
                where  num > {0} and num <= {1}  {2}", tableName = "V_MCG_11_HT_MaterialPurchaseDJ_DA";

            StringBuilder conn = null;

            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            conn = new StringBuilder();

            if (!string.IsNullOrEmpty(CGCD))
            {
                conn.Append(string.Format(" and CGCD = '{0}'", CGCD));
            }

            if (!string.IsNullOrEmpty(CGDZ))
            {
                conn.Append(string.Format(" and CGDZ = '{0}'", CGDZ));
            }

            if (!string.IsNullOrEmpty(CGCGFL))
            {
                conn.Append(string.Format(" and CGCGFL = '{0}'", CGCGFL));
            }

            if (!string.IsNullOrEmpty(CGCGFS))
            {
                conn.Append(string.Format(" and CGCGFS = '{0}'", CGCGFS));
            }

            if (!string.IsNullOrEmpty(CGYT))
            {
                conn.Append(string.Format(" and CGYT = '{0}'", CGYT));
            }

            if (!string.IsNullOrEmpty(CGGHDW))
            {
                conn.Append(string.Format(" and CGGHDW = '{0}'", CGGHDW));
            }

            if (!string.IsNullOrEmpty(CGCGFLiu))
            {
                conn.Append(string.Format(" and CGLX = '{0}'", CGCGFLiu));
            }

            start = (pagination.page - 1) * pagination.rows;

            end = pagination.page * pagination.rows;

            sql = string.Format(sql, start, end, conn, tableName, VBaseData.Common.BuildSortSQL(pagination.sidx, pagination.sord));

            VBaseData.Common.GetRecords(pagination, tableName, conn);

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public object GetSupplierInformationies(string UseUnit, int idx)
        {
            List<Dictionary<string, object>> result = null;

            string[] conn = "AND IsMaterial = '1',AND(UnitNO = UseUnit OR IsCYCJ = '1')".Split(',');

            string sql = @"SELECT
	                    UnitNO,
	                    UnitShortName,
                    UnitNO + ' ' + UnitShortName UnitNOName
                    FROM
	                    ZSJ_B1_GH_SupplierInformation
                    WHERE
	                    UseUnit = @UseUnit
                    {0}
                    ORDER BY
	                    UnitNO";
            //select UnitNO, UnitShortName  FROM ZSJ_B1_GH_SupplierInformation  where UseUnit = '使用单位'  AND(UnitNO = UseUnit OR IsCYCJ = '1')  ORDER BY UnitNO
            Dictionary<string, object> item = null;

            result = new List<Dictionary<string, object>>();

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@UseUnit", UseUnit)
            };

            sql = string.Format(sql, conn[idx]);

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }

        public Dictionary<string, string> GetSupplierInformationDics(string UseUnit, int idx)
        {
            Dictionary<string, string> result = null;

            string[] conn = "AND IsMaterial = '1',AND(UnitNO = UseUnit OR IsCYCJ = '1')".Split(',');

            string sql = @"SELECT
	                    UnitNO,
	                    UnitShortName
                    FROM
	                    ZSJ_B1_GH_SupplierInformation
                    WHERE
	                    UseUnit = @UseUnit
                    {0}
                    ORDER BY
	                    UnitNO";
            result = new Dictionary<string, string>();

            SqlParameter[] pars = new SqlParameter[] {
                new SqlParameter("@UseUnit", UseUnit)
            };

            sql = string.Format(sql, conn[idx]);

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql, pars))
            {
                while (reader.Read())
                {
                    result.Add(reader["UnitShortName"].ToString(), reader["UnitNO"].ToString());
                }
            }

            return result;
        }

        public List<Dictionary<string, object>> GetMetrialCategories()
        {
            List<Dictionary<string, object>> result = null;

            Dictionary<string, object> item = null;

            string sql = @"SELECT
	                        	REPLACE(MaterialSizes,' ','') MaterialSizes,
                                MaterialCode,
                                MaterialNO
                        FROM
	                        CLDE_51_GG_CLDEerialVarieties";

            result = new List<Dictionary<string, object>>();

            using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.GetConnection(), CommandType.Text, sql))
            {
                while (reader.Read())
                {
                    item = new Dictionary<string, object>();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        item.Add(reader.GetName(i), reader[i]);
                    }

                    result.Add(item);
                }
            }

            return result;
        }
    }
}

